﻿/*sql server 版数据库存取功能方法
 * 
 * 程序开发：Andy
 * QQ:9502855
 * 版权所有：hubs1
 * 开发时间：2009年1月20日
 * 
 * 该程序不提供给客户源码，只提供给客户编译过的文件
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
using System.Reflection;
using EZCRS.HCMS.Entity;


namespace EZCRS.HCMS.Data
{
    public class ExecSql
    {
        public ExecSql()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        public static string connstr()
        {
            try
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["mainDB"].ConnectionString;
            }
            catch
            {
                return "";
            }
        }
        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="">一些特定参数，如表名，字段名，关键字段等</param>
        /// <returns>返回DataSet</returns>
        public static DataSet AddParamFrKeyField(string TblName, string StrGetFields, string FldName, string KeyName, int PageSize, int PageIndex, string StrWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@KeyName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = TblName;
            parameters[1].Value = StrGetFields;
            parameters[2].Value = FldName;
            parameters[3].Value = KeyName;
            parameters[4].Value = PageSize;
            parameters[5].Value = PageIndex;
            parameters[6].Value = StrWhere;
            return ExecSql.GetDataSetByStoredProcedure("hfg_paginationc", parameters);
        }

        /// <summary>
        /// 执行无需返回结果的sql语句
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <returns>返回相应的比数</returns>
        public static int SqlExecNoquery(string SqlStr)
        {
            #region
            SqlConnection conn = new SqlConnection(connstr());
            int Result = 0;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                Result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return Result;
            #endregion
        }

        public static int TSqlExecNoquery(string SqlStr)
        {
            #region
            SqlConnection conn = new SqlConnection(connstr());
            SqlTransaction trans = null;
            int Result = 0;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                cmd.Transaction = trans;
                Result = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw e;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                trans.Dispose();
            }
            return Result;
            #endregion
        }

        /// <summary>
        /// 执行无需返回结果、带参数的sql语句
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="parm">参数组</param>
        /// <returns></returns>
        public static int SqlExecNoquery(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            SqlConnection conn = new SqlConnection(connstr());

            conn.Open();
            SqlCommand cmd = new SqlCommand(SqlStr, conn);

            cmd.Parameters.Clear();
            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }
            try
            {
                Result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Result = 0;
                throw e;
            }
            finally
            {
                cmd.Parameters.Clear();
                conn.Close();
                conn.Dispose();
            }
            return Result;
            #endregion
        }

        public static int TSqlExecNoquery(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            SqlConnection conn = new SqlConnection(connstr());
            SqlTransaction trans = null;

            conn.Open();
            trans = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand(SqlStr, conn);
            cmd.Transaction = trans;

            cmd.Parameters.Clear();
            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }
            try
            {
                Result = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception e)
            {
                Result = 0;
                trans.Rollback();
                throw e;
            }
            finally
            {
                cmd.Parameters.Clear();
                conn.Close();
                conn.Dispose();
                trans.Dispose();
            }
            return Result;
            #endregion
        }

        public static int SqlExecNoqueryByProcedure(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            SqlConnection conn = new SqlConnection(connstr());
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.CommandText = SqlStr;

            cmd.Parameters.Clear();
            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }

            try
            {
                conn.Open();
                Result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }

            return Result;
            #endregion
        }



        public static bool SqlExecNoqueryByProcedure(string procedurename, out SqlCommand outcmd, params SqlParameter[] cmdParms)
        {
            #region
            int Result = 0;
            outcmd = null;
            SqlConnection conn = new SqlConnection(connstr());
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.CommandText = procedurename;

            cmd.Parameters.Clear();
            foreach (SqlParameter pam in cmdParms)
            {
                cmd.Parameters.Add(pam);
            }

            try
            {
                conn.Open();
                Result = cmd.ExecuteNonQuery();
                outcmd = cmd;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }

            if (Result > 0)
                return true;
            else
                return false;

            #endregion
        }

        /// <summary>
        /// 执行返回ds的查询
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string SqlStr)
        {
            #region
            SqlConnection conn = new SqlConnection(connstr());
            DataSet ds = new DataSet();

            try
            {
                SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
                da.Fill(ds);
                da.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Dispose();
            }
            return ds;
            #endregion
        }

        /// <summary>
        /// 执行返回ds的查询
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="sqlconn">数据库连接字符串</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string SqlStr, string sqlconn)
        {
            #region
            SqlConnection conn = new SqlConnection(sqlconn);
            DataSet ds = new DataSet();

            try
            {
                SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
                da.Fill(ds);
                da.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Dispose();
            }
            return ds;
            #endregion
        }

        /// <summary>
        /// 执行带参数的查询，返回ds
        /// </summary>
        /// <param name="SqlStr">sql查询语句</param>
        /// <param name="cmdParms">参数组</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            SqlConnection conn = new SqlConnection(connstr());
            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
            da.SelectCommand.Parameters.Clear();
            foreach (SqlParameter pam in cmdParms)
            {
                da.SelectCommand.Parameters.Add(pam);
            }

            try
            {
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                da.SelectCommand.Parameters.Clear();
                da.Dispose();
                conn.Dispose();
            }
            return ds;
            #endregion
        }


        /// <summary>
        /// 执行带参数的查询，返回ds
        /// </summary>
        /// <param name="SqlStr">sql查询语句</param>
        /// <param name="cmdParms">参数组</param>
        /// <returns></returns>
        public static DataSet GetDataSetByStoredProcedure(string SqlStr, params SqlParameter[] cmdParms)
        {
            #region
            SqlConnection conn = new SqlConnection(connstr());
            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.Clear();
            foreach (SqlParameter pam in cmdParms)
            {
                da.SelectCommand.Parameters.Add(pam);
            }

            try
            {
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                da.SelectCommand.Parameters.Clear();
                da.Dispose();
                conn.Dispose();
            }
            return ds;
            #endregion
        }



        #region  2.0 方法


        public static List<T> GetTableList<T>(string TblName, string StrGetFields, string FldName, string KeyName, int PageSize, int PageIndex, string StrWhere, out int RecordCount)
        {
            DataSet ds = AddParamFrKeyField(TblName, StrGetFields, FldName, KeyName, PageSize, PageIndex, StrWhere);
            if (ds.Tables.Count < 2)
            {
                RecordCount = 0;
                return null;
            }
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
            return CreateObject<T>(ds.Tables[0]);
        }
        /// <summary>
        /// 执行sql语句，返回实体对象组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlStr"></param>
        /// <returns></returns>
        public static List<T> GetTableList<T>(string SqlStr)
        {
            DataTable dt = GetDataSet(SqlStr).Tables[0];
            return CreateObject<T>(dt);
        }

        /// <summary>
        /// 执行带参数的Sql语句，返回实体对象组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlStr"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static List<T> GetTableList<T>(string SqlStr, params SqlParameter[] cmdParms)
        {
            DataTable dt = GetDataSet(SqlStr, cmdParms).Tables[0];
            return CreateObject<T>(dt);
        }

        /// <summary>
        /// 执行带参数的存储过程，返回实体对象组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlStr"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static List<T> GetTableListByProcedure<T>(string SqlStr, params SqlParameter[] cmdParams)
        {
            DataTable dt = GetDataSetByStoredProcedure(SqlStr, cmdParams).Tables[0];
            return CreateObject<T>(dt);
        }

        /// <summary>
        /// 将DataRow 转化成实体对象
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="dr">数据行</param>
        /// <returns></returns>
        public static T CreateObject<T>(DataRow dr)
        {
            if (dr == null)
                return default(T);
            object objDataFieldAttribute = null;
            Type _type = typeof(T);
            T MyClass = Activator.CreateInstance<T>();
            PropertyInfo[] _infolist = _type.GetProperties();

            string ColmunName = "";
            foreach (PropertyInfo _info in _infolist)
            {
                ColmunName = _info.Name;
                objDataFieldAttribute = _info.GetCustomAttributes(typeof(DataFieldAttribute), false);
                if (objDataFieldAttribute != null)
                {
                    DataFieldAttribute[] colattrib = (DataFieldAttribute[])objDataFieldAttribute;
                    if (colattrib.Length > 0)
                    {
                        ColmunName = colattrib[0].FieldName;
                    }
                }

                try
                {
                    if (dr.Table.Columns.IndexOf(ColmunName) >= 0)
                    {
                        _info.SetValue(MyClass, dr[ColmunName], null);
                    }
                }
                catch { }
            }
            return MyClass;
        }

        /// <summary>
        /// 将DataRow 转化成实体对象
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="dt">数据表</param>
        /// <returns></returns>
        public static List<T> CreateObject<T>(DataTable dt)
        {
            if (dt == null)
                return null;
            List<T> tlist = new List<T>();

            object objDataFieldAttribute = null;
            Type _type = typeof(T);
            PropertyInfo[] _infolist = _type.GetProperties();

            string ColmunName = "";

            foreach (DataRow dr in dt.Rows)
            {
                T MyClass = Activator.CreateInstance<T>();
                foreach (PropertyInfo _info in _infolist)
                {
                    ColmunName = _info.Name;
                    objDataFieldAttribute = _info.GetCustomAttributes(typeof(DataFieldAttribute), false);
                    if (objDataFieldAttribute != null)
                    {
                        DataFieldAttribute[] colattrib = (DataFieldAttribute[])objDataFieldAttribute;
                        if (colattrib.Length > 0)
                        {
                            ColmunName = colattrib[0].FieldName;
                        }
                    }

                    try
                    {
                        if (dr.Table.Columns.IndexOf(ColmunName) >= 0)
                        {
                            _info.SetValue(MyClass, dr[ColmunName], null);
                        }
                    }
                    catch { }
                }
                tlist.Add(MyClass);
            }
            return tlist;
        }

        /// <summary>
        /// 新增或修改数据表数据
        /// </summary>
        /// <typeparam name="T">对应要修改或新增的实体类型</typeparam>
        /// <param name="_myclass">对应要修改或新增的实体</param>
        /// <returns></returns>
        public static bool InsertOrUpdateData<T>(T _myclass)
        {
            string sqlstr = "";
            object objDataFieldAttribute = null;
            Type _type = typeof(T);

            object objtable = _type.GetCustomAttributes(typeof(DataFieldAttribute), false);
            string tablename = "";

            if (objtable != null)
            {
                DataFieldAttribute[] tableattrib = (DataFieldAttribute[])objtable;
                if (tableattrib.Length > 0)
                    tablename = tableattrib[0].FieldName;
                else
                    tablename = _type.Name;
            }
            else
                tablename = _type.Name;

            PropertyInfo[] _infolist = _type.GetProperties();

            ArrayList ORMap = new ArrayList();
            bool isinsert = true;
            string strpk = "";
            object _obj = null;

            //获得实体和数据库对应关系
            foreach (PropertyInfo _info in _infolist)
            {
                _obj = _type.GetProperty(_info.Name).GetValue(_myclass, null);
                if (_obj != null && _obj.ToString() != "")
                {
                    objDataFieldAttribute = _info.GetCustomAttributes(typeof(DataFieldAttribute), false);
                    if (objDataFieldAttribute != null)
                    {
                        DataFieldAttribute[] colattrib = (DataFieldAttribute[])objDataFieldAttribute;
                        if (colattrib.Length > 0)
                        {
                            DataFieldAttribute _df = colattrib[0];
                            ORMap.Add(new string[] { _df.FieldName, _info.Name });
                            if (_df.PK == "pk")
                            {
                                strpk = _df.FieldName;
                                if (((int)_type.GetProperty(_info.Name).GetValue(_myclass, null)) > 0)
                                    isinsert = false;
                            }
                        }
                        else
                            ORMap.Add(new string[] { _info.Name, _info.Name });
                    }
                    else
                        ORMap.Add(new string[] { _info.Name, _info.Name });
                }
            }

            //整理入参
            SqlParameter[] pams = new SqlParameter[ORMap.Count];
            string insertsql1 = "", insertsql2 = "", updatesql = "";
            int i = 0;
            foreach (string[] mapstr in ORMap)
            {
                pams[i] = new SqlParameter("@" + mapstr[0], _type.GetProperty(mapstr[1]).GetValue(_myclass, null));
                if (strpk != mapstr[0])
                {
                    if (isinsert)
                    {

                        if (insertsql1 != "")
                        {
                            insertsql1 += ",";
                            insertsql2 += ",";
                        }
                        insertsql1 += mapstr[0];
                        insertsql2 += "@" + mapstr[0];

                    }
                    else
                    {
                        if (updatesql != "")
                            updatesql += ",";
                        updatesql += mapstr[0] + "=@" + mapstr[0];
                    }
                }
                i++;
            }
            if (isinsert)
            {
                sqlstr = "insert into " + tablename + "(" + insertsql1 + ") values(" + insertsql2 + ")";
            }
            else
            {
                if (strpk == "")//如果没有主键，则不能进行更新操作
                    return false;
                sqlstr = "update " + tablename + " set " + updatesql + " where " + strpk + "=@" + strpk;
            }

            //执行查询
            if (ExecSql.SqlExecNoquery(sqlstr, pams) > 0)
                return true;
            else
                return false;
        }
        #endregion
    }

}
